package com.example.shopping.repository;

import com.example.shopping.entity.Order;
import com.example.shopping.entity.dto.OrderDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;


public interface OrderRepository extends JpaRepository<Order,Integer> {

        @Query("SELECT new com.example.shopping.entity.dto.OrderDTO(o.oid, u.username, g.goodsname,o.mailingaddress,o.rphone, o.quantity, o.money, o.buytime) " +
                "FROM Order o JOIN User u ON o.uid = u.uid JOIN Goods g ON o.gid = g.gid ORDER BY o.buytime DESC")
        List<OrderDTO> findOrderDTOs();





        @Query("SELECT new com.example.shopping.entity.dto.OrderDTO(" +
                "o.oid, u.username, g.goodsname, o.mailingaddress, o.rphone, o.receiver, o.buytime, o.quantity, o.money) " +
                "FROM Order o " +
                "LEFT JOIN User u ON o.uid = u.uid " +
                "LEFT JOIN Goods g ON o.gid = g.gid " +
                "WHERE " +
                "(:username IS NULL OR u.username LIKE %:username%) AND " +
                "(:goodsname IS NULL OR g.goodsname LIKE %:goodsname%) AND " +
                "(:mailingaddress IS NULL OR o.mailingaddress LIKE %:mailingaddress%) AND " +
                "(:rphone IS NULL OR o.rphone LIKE %:rphone%) AND " +
                "(:receiver IS NULL OR o.receiver LIKE %:receiver%) AND " +
                "(:buytimebegin IS NULL OR o.buytime >= :buytimebegin) AND " +
                "(:buytimeend IS NULL OR o.buytime <= :buytimeend)")
        Page<OrderDTO> findOrdersWithCriteria(
                @Param("username") String username,
                @Param("goodsname") String goodsname,
                @Param("mailingaddress") String mailingaddress,
                @Param("rphone") String rphone,
                @Param("receiver") String receiver,
                @Param("buytimebegin") LocalDateTime buytimebegin,
                @Param("buytimeend") LocalDateTime buytimeend,
                Pageable pageable);

    @Query("SELECT new com.example.shopping.entity.dto.OrderDTO(o.oid, u.username, g.goodsname,o.mailingaddress,o.rphone, o.quantity, o.money, o.buytime) " +
            "FROM Order o JOIN User u ON o.uid = u.uid JOIN Goods g ON o.gid = g.gid where o.uid =:uid ORDER BY o.buytime DESC")
    List<OrderDTO> findOrdersByUid(int uid);
}
